package rsj.service.export.org;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import common.util.DateTimeFormatterUtil;
import rsj.api.org.entity.ExportOrgCurrentTable;
import rsj.api.org.entity.UsageReportParam;
import rsj.service.export.IExportTemplateMethod;
import rsj.service.usage.org.IOrgCalculateUsage;

import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.time.LocalDate;
import java.util.List;
import java.util.Objects;
import java.util.Optional;

/**
 * 机构管理-导出当前环境表格
 *
 * @author yuanqinglong
 * @since 2021/3/5 15:34
 */
public interface IOrgExportCurrentTable extends IExportTemplateMethod, IOrgCalculateUsage {


    /**
     * 导出当前表格sql
     *
     * @param usageReportParam 参数
     * @return sql
     */
    static String exportCurrentTableQueryStatement(UsageReportParam usageReportParam) {
        String statDate = Optional.ofNullable(usageReportParam.getStatDate()).map(localDate ->
                DateTimeFormatterUtil.localDateFormatterToStringDate(usageReportParam.getStatDate())).orElse(DateTimeFormatterUtil.localDateFormatterToStringDate(LocalDate.now().minusDays(1)));

        String sql = "SELECT stat_orgp_id,stat_org_id,stat_org_sname,STRING_AGG(stat_prod_uid,',') AS stat_prod_uid,STRING_AGG(stat_prod_name,',') AS stat_prod_name, " +
                "CONCAT('截至',MAX('" + statDate + "')) AS stat_date, " +
                "MAX(orgp.orgp_buyamount) AS orgp_buyamount ,STRING_AGG(temp.stat_useamount,',') AS stat_useamount " +
                "FROM " +
                "(SELECT stat_orgp_id,stat_org_id,max(stat_org_sname) as stat_org_sname,stat_prod_uid,stat_prod_name,sum(stat_useamount::INTEGER)::text as stat_useamount " +
                "FROM sys.sys_stat WHERE stat_env = #{environment} AND stat_org_id = #{statOrgId} ";
        if (Objects.nonNull(usageReportParam.getStatDate())) {
            sql += " AND stat_date < #{statDate} ";
        }
        sql += " GROUP BY stat_orgp_id,stat_org_id,stat_prod_uid,stat_prod_name " +
                ") temp " +
                " LEFT JOIN sys.sys_orgp orgp ON orgp.orgp_id = stat_orgp_id " +
                " GROUP BY stat_orgp_id,stat_org_id,stat_org_sname ";
        return sql;
    }


    /**
     * 将生成的文件写入OutputStream
     *
     * @param jsonString 查询的数据
     * @param sheetName  导出的工作表名称
     * @return 写入文件的路径
     */
    @Override
    default OutputStream writeDataToTheOutputStream(String jsonString, String sheetName) {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        List<ExportOrgCurrentTable> downloadExportEntities = JSON.parseArray(jsonString, ExportOrgCurrentTable.class);
        EasyExcelFactory.write(outputStream, ExportOrgCurrentTable.class).autoCloseStream(true).excelType(ExcelTypeEnum.XLSX).sheet().doWrite(downloadExportEntities);
        return outputStream;
    }


    /**
     * 写数据到excel
     *
     * @param jsonData    json数据
     * @param excelWriter 优秀的作家
     * @param sheetNum    表num
     */
    @Override
    default void writeDataToTheExcelWriter(String jsonData, ExcelWriter excelWriter, int sheetNum) {
        WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetNum).head(ExportOrgCurrentTable.class).build();
        excelWriter.write(JSON.parseArray(jsonData, ExportOrgCurrentTable.class), writeSheet);
    }
}
